﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using UNDP_Projects.Data_Access;

/*Reference to the itextsharp*/
using iTextSharp;
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.Text;
//For the FileStream
using System.IO;
//For the DataTable
using System.Data;

namespace UNDP_Projects.Classes
{
    public partial class PdfCreator : System.Web.UI.Page
    {
        UNDPEntities db = new UNDPEntities();

        public void PopulateAcroFields(long project, long reportPeriod, long year, PdfStamper pdfStamper)
        {
            //Get the form fields
            AcroFields pdfFormFields = pdfStamper.AcroFields;
            try
            {
                //=================================================+
                //          <BEGIN> POPULATE ACROFIELDS            |
                //=================================================+

                var selectedProject = (from p in db.project_reports
                                        where
                                            p.project_id == project &&
                                            p.year_id == year &&
                                            p.report_period_id == reportPeriod
                                        select new
                                        {
                                            p.project.project1,
                                            p.year.year1,
                                            p.report_period.report_period1,
                                            p.project.program.program1,
                                            p.project_status.status,
                                            p.donors,
                                            p.project_editor,
                                            p.gender_mainstreaming,
                                            p.partnerships,
                                            p.lessons,
                                            p.upcoming_quarter,
                                            p.peace_and_conflict,
                                            p.approved_budget_cfs,
                                            p.total_cfs,
                                            p.balance_cfs,
                                            p.delivery_cfs,
                                            p.saved_date,
                                            p.submission_date,
                                            p.approved_date,
                                            p.end_year_summary
                                        });

                var selectedProject_ActivityUpdates = (from au in db.activity_updates
                                                        where
                                                            au.project_id == project &&
                                                            au.year_id == year &&
                                                            au.report_period_id == reportPeriod
                                                        select new
                                                        {
                                                            au.region_id,
                                                            au.activity_update
                                                        });

                var selectedProject_ContextualAnalysis = (from ca in db.contextual_analysis
                                                            where
                                                                ca.project_id == project &&
                                                                ca.year_id == year &&
                                                                ca.report_period_id == reportPeriod
                                                            select new
                                                            {
                                                                ca.region_id,
                                                                ca.contextual_analysis1
                                                            });


                var ProjectAcronyms = (from data in db.acronym_list
                                        where
                                        data.project_id == project
                                        select new
                                        {
                                            data.acronyms
                                        });


                foreach (var p in selectedProject)
                {
                    pdfFormFields.SetField("ProjectTitle", p.project1.Trim());
                    if (reportPeriod == 4)
                    {
                        try
                        {
                            pdfFormFields.SetField("project_summary", p.end_year_summary.Trim());
                        }
                        catch
                        {
                            pdfFormFields.SetField("project_summary", "N/A");
                        }
                    }
                    try
                    {
                        pdfFormFields.SetField("Partnerships", p.partnerships);
                    }
                    catch
                    {
                        pdfFormFields.SetField("Partnerships", "NA");
                    }
                    try
                    {
                        pdfFormFields.SetField("Peace&Conflict", p.peace_and_conflict);
                    }
                    catch
                    {
                        pdfFormFields.SetField("Peace&Conflict", "NA");
                    }
                    try
                    {
                        pdfFormFields.SetField("GenderMainstreaming", p.gender_mainstreaming);
                    }
                    catch
                    {
                        pdfFormFields.SetField("GenderMainstreaming", "NA");
                    }
                    try
                    {
                        pdfFormFields.SetField("UpcomingQuarter", p.upcoming_quarter);
                    }
                    catch
                    {
                        pdfFormFields.SetField("UpcomingQuarter", "NA");
                    }
                    try
                    {
                        pdfFormFields.SetField("Lessons", p.lessons);
                    }
                    catch
                    {
                        pdfFormFields.SetField("Lessons", "NA");
                    }

                    pdfFormFields.SetField("approved_budget_cfs", p.approved_budget_cfs);
                    pdfFormFields.SetField("total_cfs", p.total_cfs);
                    pdfFormFields.SetField("balance_cfs", p.balance_cfs);
                    pdfFormFields.SetField("delivery_cfs", p.delivery_cfs);
                    pdfFormFields.SetField("prog", p.program1);
                    string report = p.report_period1.Trim() + " " + p.year1.Trim();
                    pdfFormFields.SetField("reportperiod", report);
                    pdfFormFields.SetField("status", p.status);
                    pdfFormFields.SetField("date", p.submission_date);
                    pdfFormFields.SetField("authors", p.project_editor);

                    char[] separator = new char[] { '-' };
                    string[] donorList = p.donors.Trim().Split(separator);
                    Paragraph para = new Paragraph();
                    List list = new List();
                    for (int i = 0; i <= donorList.Length - 1; i++)
                    {
                        //pdfFormFields.SetField("donors", para.Content);
                        para.Add(donorList[i] + "\n");
                    }
                    para.Add(list);
                    pdfFormFields.SetField("donors", para.Content);
                }

                if (selectedProject_ContextualAnalysis != null)
                {
                    foreach (var ca in selectedProject_ContextualAnalysis)
                    {
                        if (ca.region_id == 1)
                        {
                            pdfFormFields.SetField("C.A.SL", ca.contextual_analysis1);
                        }
                        else if (ca.region_id == 2)
                        {
                            pdfFormFields.SetField("C.A.PL", ca.contextual_analysis1);
                        }
                        else if (ca.region_id == 3)
                        {
                            pdfFormFields.SetField("C.A.SC", ca.contextual_analysis1);
                        }
                    }
                }

                if (selectedProject_ActivityUpdates != null)
                {
                    foreach (var au in selectedProject_ActivityUpdates)
                    {
                        if (au.region_id == 1)
                        {
                            pdfFormFields.SetField("ACT.UPD.SL", au.activity_update);
                        }
                        else if (au.region_id == 2)
                        {
                            pdfFormFields.SetField("ACT.UPD.PL", au.activity_update);
                        }
                        else if (au.region_id == 3)
                        {
                            pdfFormFields.SetField("ACT.UPD.SC", au.activity_update);
                        }
                    }
                }

                if (ProjectAcronyms != null)
                {
                    foreach (var acronyms in ProjectAcronyms)
                    {
                        pdfFormFields.SetField("acronyms", acronyms.acronyms);
                    }
                }

                //===============================================+
                //          <END> POPULATE ACROFIELDS            |
                //===============================================+
            }
            catch (Exception ex)
            {
                throw new Exception("PDF ERROR " + ex.Message + " " + ex.StackTrace);
            }
        }

        public void GenerateIndicatorsTable(long project, long reportPeriod, long year,PdfReader pdfReader, PdfStamper pdfStamper)
        {
            try
            {
                //============================================================+
                //          <BEGIN> GENERATE THE INDICATORS TABLE             |
                //============================================================+

                PdfPTable table = new PdfPTable(3);
                //Actual width of the table in points
                //table.TotalWidth = 500f;

                float[] widths = new float[] { 30f, 494f, 14.5f };
                table.SetTotalWidth(widths);

                table.SpacingBefore = 20f;
                table.SpacingAfter = 30f;

                string fontPath = System.Web.HttpContext.Current.Server.MapPath("~/Styles/");
                BaseFont MyriadPro = BaseFont.CreateFont(fontPath + "MyriadPro-Regular.otf", BaseFont.CP1252, BaseFont.EMBEDDED);
                Font tableDataFont = new Font(MyriadPro, 9f, Font.NORMAL, BaseColor.DARK_GRAY);

                Font colorIndicatorDataFont = new Font(MyriadPro, 9f, Font.NORMAL, BaseColor.WHITE);

                var cpdIndicator = (from c in db.cpd_output_results
                                    where c.project_id == project &&
                                    c.year_id == year &&
                                    c.report_period_id == reportPeriod 
                                    orderby c.region_id ascending 
                                    select new
                                    {
                                        c.region.region1,
                                        c.cpd_output_indicator_id,
                                        c.cpd_output_indicators.cpd_output_indicator_question,
                                        c.indicator_status_id,
                                        c.result,
                                        c.comment
                                    });

                var nonCpdIndicator = (from n in db.non_cpd_output_indicators
                                        where n.project_id == project &&
                                        n.year_id == year &&
                                        n.report_period_id == reportPeriod 
                                        orderby n.region_id ascending 
                                        select new
                                        {
                                            n.region.region1,
                                            n.cpd_output_id,
                                            n.non_cpd_outp_indicator_question,
                                            n.indicator_status_id,
                                            n.result,
                                            n.comment
                                        });

                var processIndicator = (from p in db.internal_process_indicators
                                        where p.project_id == project &&
                                        p.year_id == year &&
                                        p.report_period_id == reportPeriod 
                                        orderby p.region_id ascending 
                                        select new
                                        {
                                            p.region.region1,
                                            p.internal_process_question,
                                            p.indicator_status_id,
                                            p.result,
                                            p.comment
                                        });

                foreach (var row in cpdIndicator)
                {
                    PdfPCell myCell1 = new PdfPCell(new Phrase(row.region1, tableDataFont));
                    myCell1.BorderColor = BaseColor.WHITE;
                    myCell1.BorderWidth = 1f;
                    myCell1.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell1.HorizontalAlignment = Element.ALIGN_CENTER;
                    table.AddCell(myCell1);

                    PdfPTable t1 = new PdfPTable(2);
                    float[] t1Widths = new float[] { 50f, 444f };
                    t1.SetTotalWidth(t1Widths);
                    PdfPCell myCell2 = new PdfPCell(new Phrase("Indicator", tableDataFont));
                    myCell2.BorderColor = BaseColor.WHITE;
                    myCell2.BorderWidth = 1f;
                    myCell2.FixedHeight = 25f;
                    myCell2.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell2.HorizontalAlignment = Element.ALIGN_CENTER;
                    t1.AddCell(myCell2);
                    PdfPCell myCell3 = new PdfPCell(new Phrase(row.cpd_output_indicator_id + " " + row.cpd_output_indicator_question, tableDataFont));
                    myCell3.BorderColor = BaseColor.WHITE;
                    myCell3.BorderWidth = 1f;
                    myCell3.FixedHeight = 25f;
                    myCell3.VerticalAlignment = Element.ALIGN_MIDDLE;
                    t1.AddCell(myCell3);
                    PdfPCell myCell5 = new PdfPCell(new Phrase("Result", tableDataFont));
                    myCell5.BorderColor = BaseColor.WHITE;
                    myCell5.BorderWidth = 1f;
                    myCell5.FixedHeight = 35f;
                    myCell5.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell5.HorizontalAlignment = Element.ALIGN_CENTER;
                    t1.AddCell(myCell5);
                    PdfPCell myCell6 = new PdfPCell(new Phrase(row.result, tableDataFont));
                    myCell6.BorderColor = BaseColor.WHITE;
                    myCell6.BorderWidth = 1f;
                    myCell6.FixedHeight = 35f;//This is the result height
                    myCell6.VerticalAlignment = Element.ALIGN_MIDDLE;
                    t1.AddCell(myCell6);
                    PdfPCell myCell7 = new PdfPCell(new Phrase("Comment", tableDataFont));
                    myCell7.BorderColor = BaseColor.WHITE;
                    myCell7.BorderWidth = 1f;
                    myCell7.FixedHeight = 25f;
                    myCell7.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell7.HorizontalAlignment = Element.ALIGN_CENTER;
                    t1.AddCell(myCell7);
                    PdfPCell myCell8 = new PdfPCell(new Phrase(row.comment, tableDataFont));
                    myCell8.BorderColor = BaseColor.WHITE;
                    myCell8.BorderWidth = 1f;
                    myCell8.FixedHeight = 25f;
                    myCell8.VerticalAlignment = Element.ALIGN_MIDDLE;
                    t1.AddCell(myCell8);
                    PdfPCell t1Cell = new PdfPCell(t1);
                    //t1Cell.BackgroundColor = BaseColor.WHITE;
                    table.AddCell(t1Cell);

                    if (row.indicator_status_id == 1)
                    {
                        PdfPCell myCell4 = new PdfPCell(new Phrase("R", colorIndicatorDataFont));
                        //myCell4.Rowspan = 3;
                        myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                        myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                        myCell4.BackgroundColor = BaseColor.RED;
                        myCell4.BorderColor = BaseColor.WHITE;
                        myCell4.BorderWidth = 1f;
                        table.AddCell(myCell4);
                    }
                    else if (row.indicator_status_id == 2)
                    {
                        PdfPCell myCell4 = new PdfPCell(new Phrase("Y", colorIndicatorDataFont));
                        //myCell4.Rowspan = 3;
                        myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                        myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                        myCell4.BackgroundColor = BaseColor.YELLOW;
                        myCell4.BorderColor = BaseColor.WHITE;
                        myCell4.BorderWidth = 1f;
                        table.AddCell(myCell4);
                    }
                    else if (row.indicator_status_id == 3)
                    {
                        PdfPCell myCell4 = new PdfPCell(new Phrase("G", colorIndicatorDataFont));
                        //myCell4.Rowspan = 3;
                        myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                        myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                        myCell4.BackgroundColor = BaseColor.GREEN;
                        myCell4.BorderColor = BaseColor.WHITE;
                        myCell4.BorderWidth = 1f;
                        table.AddCell(myCell4);
                    }
                }

                foreach (var row in nonCpdIndicator)
                {
                    PdfPCell myCell1 = new PdfPCell(new Phrase(row.region1, tableDataFont));
                    myCell1.BorderColor = BaseColor.WHITE;
                    myCell1.BorderWidth = 1f;
                    myCell1.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell1.HorizontalAlignment = Element.ALIGN_CENTER;
                    table.AddCell(myCell1);

                    PdfPTable t1 = new PdfPTable(2);
                    float[] t1Widths = new float[] { 50f, 444f };
                    t1.SetTotalWidth(t1Widths);
                    PdfPCell myCell2 = new PdfPCell(new Phrase("Indicator", tableDataFont));
                    myCell2.BorderColor = BaseColor.WHITE;
                    myCell2.BorderWidth = 1f;
                    myCell2.FixedHeight = 25f;
                    myCell2.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell2.HorizontalAlignment = Element.ALIGN_CENTER;
                    t1.AddCell(myCell2);
                    //PdfPCell myCell3 = new PdfPCell(new Phrase(row.cpd_output_id + " " + row.non_cpd_outp_indicator_question, tableDataFont));
                    PdfPCell myCell3 = new PdfPCell(new Phrase(row.non_cpd_outp_indicator_question, tableDataFont));
                    myCell3.BorderColor = BaseColor.WHITE;
                    myCell3.BorderWidth = 1f;
                    myCell3.FixedHeight = 25f;
                    myCell3.VerticalAlignment = Element.ALIGN_MIDDLE;
                    t1.AddCell(myCell3);
                    PdfPCell myCell5 = new PdfPCell(new Phrase("Result", tableDataFont));
                    myCell5.BorderColor = BaseColor.WHITE;
                    myCell5.BorderWidth = 1f;
                    myCell5.FixedHeight = 35f;
                    myCell5.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell5.HorizontalAlignment = Element.ALIGN_CENTER;
                    t1.AddCell(myCell5);
                    PdfPCell myCell6 = new PdfPCell(new Phrase(row.result, tableDataFont));
                    myCell6.BorderColor = BaseColor.WHITE;
                    myCell6.BorderWidth = 1f;
                    myCell6.FixedHeight = 35f;
                    myCell6.VerticalAlignment = Element.ALIGN_MIDDLE;
                    t1.AddCell(myCell6);
                    PdfPCell myCell7 = new PdfPCell(new Phrase("Comment", tableDataFont));
                    myCell7.BorderColor = BaseColor.WHITE;
                    myCell7.BorderWidth = 1f;
                    myCell7.FixedHeight = 25f;
                    myCell7.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell7.HorizontalAlignment = Element.ALIGN_CENTER;
                    t1.AddCell(myCell7);
                    PdfPCell myCell8 = new PdfPCell(new Phrase(row.comment, tableDataFont));
                    myCell8.BorderColor = BaseColor.WHITE;
                    myCell8.BorderWidth = 1f;
                    myCell8.FixedHeight = 25f;
                    myCell8.VerticalAlignment = Element.ALIGN_MIDDLE;
                    t1.AddCell(myCell8);
                    PdfPCell t1Cell = new PdfPCell(t1);
                    //t1Cell.BackgroundColor = BaseColor.WHITE;
                    table.AddCell(t1Cell);

                    if (row.indicator_status_id == 1)
                    {
                        PdfPCell myCell4 = new PdfPCell(new Phrase("R", colorIndicatorDataFont));
                        //myCell4.Rowspan = 3;
                        myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                        myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                        myCell4.BackgroundColor = BaseColor.RED;
                        myCell4.BorderColor = BaseColor.WHITE;
                        myCell4.BorderWidth = 1f;
                        table.AddCell(myCell4);
                    }
                    else if (row.indicator_status_id == 2)
                    {
                        PdfPCell myCell4 = new PdfPCell(new Phrase("Y", colorIndicatorDataFont));
                        //myCell4.Rowspan = 3;
                        myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                        myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                        myCell4.BackgroundColor = BaseColor.YELLOW;
                        myCell4.BorderColor = BaseColor.WHITE;
                        myCell4.BorderWidth = 1f;
                        table.AddCell(myCell4);
                    }
                    else if (row.indicator_status_id == 3)
                    {
                        PdfPCell myCell4 = new PdfPCell(new Phrase("G", colorIndicatorDataFont));
                        //myCell4.Rowspan = 3;
                        myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                        myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                        myCell4.BackgroundColor = BaseColor.GREEN;
                        myCell4.BorderColor = BaseColor.WHITE;
                        myCell4.BorderWidth = 1f;
                        table.AddCell(myCell4);
                    }
                }

                foreach (var row in processIndicator)
                {
                    PdfPCell myCell1 = new PdfPCell(new Phrase(row.region1, tableDataFont));
                    myCell1.BorderColor = BaseColor.WHITE;
                    myCell1.BorderWidth = 1f;
                    myCell1.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell1.HorizontalAlignment = Element.ALIGN_CENTER;
                    table.AddCell(myCell1);

                    PdfPTable t1 = new PdfPTable(2);
                    float[] t1Widths = new float[] { 50f, 444f };
                    t1.SetTotalWidth(t1Widths);
                    PdfPCell myCell2 = new PdfPCell(new Phrase("Indicator", tableDataFont));
                    myCell2.BorderColor = BaseColor.WHITE;
                    myCell2.BorderWidth = 1f;
                    myCell2.FixedHeight = 25f;
                    myCell2.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell2.HorizontalAlignment = Element.ALIGN_CENTER;
                    t1.AddCell(myCell2);
                    PdfPCell myCell3 = new PdfPCell(new Phrase(row.internal_process_question, tableDataFont));
                    myCell3.BorderColor = BaseColor.WHITE;
                    myCell3.BorderWidth = 1f;
                    myCell3.FixedHeight = 25f;
                    myCell3.VerticalAlignment = Element.ALIGN_MIDDLE;
                    t1.AddCell(myCell3);
                    PdfPCell myCell5 = new PdfPCell(new Phrase("Result", tableDataFont));
                    myCell5.BorderColor = BaseColor.WHITE;
                    myCell5.BorderWidth = 1f;
                    myCell5.FixedHeight = 35f;
                    myCell5.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell5.HorizontalAlignment = Element.ALIGN_CENTER;
                    t1.AddCell(myCell5);
                    PdfPCell myCell6 = new PdfPCell(new Phrase(row.result, tableDataFont));
                    myCell6.BorderColor = BaseColor.WHITE;
                    myCell6.BorderWidth = 1f;
                    myCell6.FixedHeight = 35f;
                    myCell6.VerticalAlignment = Element.ALIGN_MIDDLE;
                    t1.AddCell(myCell6);
                    PdfPCell myCell7 = new PdfPCell(new Phrase("Comment", tableDataFont));
                    myCell7.BorderColor = BaseColor.WHITE;
                    myCell7.BorderWidth = 1f;
                    myCell7.FixedHeight = 25f;
                    myCell7.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell7.HorizontalAlignment = Element.ALIGN_CENTER;
                    t1.AddCell(myCell7);
                    PdfPCell myCell8 = new PdfPCell(new Phrase(row.comment, tableDataFont));
                    myCell8.BorderColor = BaseColor.WHITE;
                    myCell8.BorderWidth = 1f;
                    myCell8.FixedHeight = 25f;
                    myCell8.VerticalAlignment = Element.ALIGN_MIDDLE;
                    t1.AddCell(myCell8);
                    PdfPCell t1Cell = new PdfPCell(t1);
                    //t1Cell.BackgroundColor = BaseColor.WHITE;
                    table.AddCell(t1Cell);

                    if (row.indicator_status_id == 1)
                    {
                        PdfPCell myCell4 = new PdfPCell(new Phrase("R", colorIndicatorDataFont));
                        //myCell4.Rowspan = 3;
                        myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                        myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                        myCell4.BackgroundColor = BaseColor.RED;
                        myCell4.BorderColor = BaseColor.WHITE;
                        myCell4.BorderWidth = 1f;
                        table.AddCell(myCell4);
                    }
                    else if (row.indicator_status_id == 2)
                    {
                        PdfPCell myCell4 = new PdfPCell(new Phrase("Y", colorIndicatorDataFont));
                        //myCell4.Rowspan = 3;
                        myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                        myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                        myCell4.BackgroundColor = BaseColor.YELLOW;
                        myCell4.BorderColor = BaseColor.WHITE;
                        myCell4.BorderWidth = 1f;
                        table.AddCell(myCell4);
                    }
                    else if (row.indicator_status_id == 3)
                    {
                        PdfPCell myCell4 = new PdfPCell(new Phrase("G", colorIndicatorDataFont));
                        //myCell4.Rowspan = 3;
                        myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                        myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                        myCell4.BackgroundColor = BaseColor.GREEN;
                        myCell4.BorderColor = BaseColor.WHITE;
                        myCell4.BorderWidth = 1f;
                        table.AddCell(myCell4);
                    }
                }
                IPdfPTableEvent tableEvent = new AlternatingBackground();
                table.TableEvent = tableEvent;

                //PRINT OUT THE TABLE
                int pageRows = 0;
                int currentPage;
                int newPage;
                if (reportPeriod == 4 && year >= 2)
                {
                    currentPage = 3;
                    newPage = 4;
                }
                else
                {
                    currentPage = 2;
                    newPage = 3;
                }
                while (pageRows <= table.Rows.Count)
                {
                    table.WriteSelectedRows(pageRows, pageRows + 8, 37, 735, pdfStamper.GetOverContent(currentPage));
                    pageRows += 8;

                    if (pageRows >= table.Rows.Count)
                    {
                        break;
                    }
                    pdfStamper.InsertPage(newPage, pdfReader.GetPageSize(currentPage));
                    PdfContentByte content = pdfStamper.GetOverContent(newPage);
                    PdfImportedPage page;
                    if (reportPeriod == 4 && year >= 2)
                    {
                        page = pdfStamper.GetImportedPage(pdfReader, 3);
                    }
                    else
                    {
                        page = pdfStamper.GetImportedPage(pdfReader, 2);
                    }
                    content.AddTemplate(page, 0, 0);
                    newPage++;
                    currentPage++;
                }
                //END PRINT OUT THE TABLE
                

                /*//PRINT OUT THE TABLE
                //CREATE A NEW PAGE IF IT DOES NOT FIT IN THE ORIGINAL PAGE
                if (table.Rows.Count <= 8)
                {
                    table.WriteSelectedRows(0, -1, 37, 735, pdfStamper.GetOverContent(2));
                }
                else
                {
                    int newPage = 3;
                    int currentPage = 2;
                    int lastLinePrinted = 0;
                    int maxLine = 8;
                    bool lastPage = false;
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        // must decrement the table.Rows.Count here, 
                        // otherwise it'll never enter the code below, we'll
                        // increment it back when we reach the last page
                        if ((i > 0 && i % 8 == 0) || i == table.Rows.Count - 1)
                        {
                            PdfContentByte cb = pdfStamper.GetOverContent(currentPage);
                            table.WriteSelectedRows(lastLinePrinted, maxLine, 37, 735, cb);

                            if (!lastPage)
                            {
                                pdfStamper.InsertPage(newPage, pdfReader.GetPageSize(2));
                                //Duplicate the old page templete to the new page
                                PdfContentByte content = pdfStamper.GetOverContent(newPage);
                                PdfImportedPage page = pdfStamper.GetImportedPage(pdfReader, 2);
                                content.AddTemplate(page, 0, 0);

                                //PdfContentByte cb2 = pdfStamper.GetOverContent(currentPage);
                                table.WriteSelectedRows(lastLinePrinted, maxLine, 37, 735, cb);

                                newPage++;
                                currentPage++;
                                lastLinePrinted = maxLine;
                                maxLine += 8;
                            }
                            if (maxLine > table.Rows.Count)
                            {
                                maxLine = table.Rows.Count + 1;
                                lastPage = true;
                            }
                        }
                        // This prints out the last row incase a single
                        // row remains and flows out to the last page
                        PdfContentByte cb2 = pdfStamper.GetOverContent(currentPage);
                        table.WriteSelectedRows(lastLinePrinted, maxLine, 37, 735, cb2);
                    }
                }*/
                //=========================================================+
                //          <END> GENERATE THE INDICATOR TABLE             |
                //=========================================================+
            }
            catch (Exception ex)
            {
                throw new Exception("PDF ERROR " + ex.Message + " " + ex.StackTrace);
            }
        }

        public void _GenerateIndicatorsTable(long project, long reportPeriod, long year, PdfReader pdfReader, PdfStamper pdfStamper)
        {
            try
            {
                //============================================================+
                //          <BEGIN> GENERATE THE INDICATORS TABLE             |
                //============================================================+

                PdfPTable table = new PdfPTable(3);
                //Actual width of the table in points
                //table.TotalWidth = 500f;

                float[] widths = new float[] { 30f, 494f, 14.5f };//Relative col widths in proportions
                table.SetTotalWidth(widths);

                table.SpacingBefore = 20f;
                table.SpacingAfter = 30f;

                string fontPath = System.Web.HttpContext.Current.Server.MapPath("~/Styles/");
                BaseFont MyriadPro = BaseFont.CreateFont(fontPath + "MyriadPro-Regular.otf", BaseFont.CP1252, BaseFont.EMBEDDED);
                Font tableDataFont = new Font(MyriadPro, 9f, Font.NORMAL, BaseColor.DARK_GRAY);

                Font colorIndicatorDataFont = new Font(MyriadPro, 9f, Font.NORMAL, BaseColor.WHITE);

                var SubOutcomes = (from data in db.sub_outcome_results
                                   where data.project_id == project
                                   && data.year_id == year
                                   && data.report_period_id == reportPeriod
                                   orderby data.sub_outcome_id ascending
                                   select new
                                   {
                                       data.sub_outcome_id,
                                       data.sub_outcomes.sub_outcome,
                                       data.result
                                   });

                var NullNonCpdIndicator = (from n in db.non_cpd_output_indicators
                                           where n.project_id == project &&
                                           n.year_id == year &&
                                           n.report_period_id == reportPeriod &&
                                           n.cpd_outputs.sub_outcomes.sub_outcome_id == null
                                           orderby n.region_id ascending
                                           select new
                                           {
                                               n.region.region1,
                                               n.cpd_output_id,
                                               n.non_cpd_outp_indicator_question,
                                               n.indicator_status_id,
                                               n.result,
                                               n.comment
                                           });

                var processIndicator = (from p in db.internal_process_indicators
                                        where p.project_id == project &&
                                        p.year_id == year &&
                                        p.report_period_id == reportPeriod
                                        orderby p.region_id ascending
                                        select new
                                        {
                                            p.region.region1,
                                            p.internal_process_question,
                                            p.indicator_status_id,
                                            p.result,
                                            p.comment
                                        });

                foreach (var subOutcomeData in SubOutcomes)
                {
                    var cpdIndicator = (from c in db.cpd_output_results
                                        where c.project_id == project &&
                                        c.year_id == year &&
                                        c.report_period_id == reportPeriod &&
                                        c.cpd_output_indicators.cpd_outputs.sub_outcomes.sub_outcome_id == subOutcomeData.sub_outcome_id
                                        orderby c.region_id ascending
                                        select new
                                        {
                                            c.region.region1,
                                            c.cpd_output_indicator_id,
                                            c.cpd_output_indicators.cpd_output_indicator_question,
                                            c.indicator_status_id,
                                            c.result,
                                            c.comment
                                        });

                    var nonCpdIndicator = (from n in db.non_cpd_output_indicators
                                           where n.project_id == project &&
                                           n.year_id == year &&
                                           n.report_period_id == reportPeriod &&
                                           n.cpd_outputs.sub_outcomes.sub_outcome_id == subOutcomeData.sub_outcome_id
                                           orderby n.region_id ascending
                                           select new
                                           {
                                               n.region.region1,
                                               n.cpd_output_id,
                                               n.non_cpd_outp_indicator_question,
                                               n.indicator_status_id,
                                               n.result,
                                               n.comment
                                           });

                    PdfPCell C1 = new PdfPCell(new Phrase(subOutcomeData.sub_outcome_id + " " + subOutcomeData.sub_outcome, tableDataFont));
                    C1.Colspan = 3;
                    C1.BorderColor = BaseColor.WHITE;
                    C1.BackgroundColor = BaseColor.LIGHT_GRAY;
                    C1.BorderWidth = 1f;
                    C1.VerticalAlignment = Element.ALIGN_MIDDLE;
                    C1.HorizontalAlignment = Element.ALIGN_LEFT;
                    C1.NoWrap = false;
                    table.AddCell(C1);

                    PdfPCell C2 = new PdfPCell(new Phrase(subOutcomeData.result, tableDataFont));
                    C2.Colspan = 3;
                    C2.BorderColor = BaseColor.WHITE;
                    C2.BackgroundColor = BaseColor.LIGHT_GRAY;
                    C2.BorderWidth = 1f;
                    C2.VerticalAlignment = Element.ALIGN_MIDDLE;
                    C2.HorizontalAlignment = Element.ALIGN_LEFT;
                    table.AddCell(C2);

                    foreach (var row in cpdIndicator)
                    {
                        PdfPCell myCell1 = new PdfPCell(new Phrase(row.region1, tableDataFont));
                        myCell1.BorderColor = BaseColor.WHITE;
                        myCell1.BorderWidth = 1f;
                        myCell1.VerticalAlignment = Element.ALIGN_MIDDLE;
                        myCell1.HorizontalAlignment = Element.ALIGN_CENTER;
                        table.AddCell(myCell1);

                        PdfPTable t1 = new PdfPTable(2);
                        float[] t1Widths = new float[] { 50f, 444f };
                        t1.SetTotalWidth(t1Widths);
                        PdfPCell myCell2 = new PdfPCell(new Phrase("Indicator", tableDataFont));
                        myCell2.BorderColor = BaseColor.WHITE;
                        myCell2.BorderWidth = 1f;
                        myCell2.FixedHeight = 25f;
                        myCell2.VerticalAlignment = Element.ALIGN_MIDDLE;
                        myCell2.HorizontalAlignment = Element.ALIGN_CENTER;
                        t1.AddCell(myCell2);
                        PdfPCell myCell3 = new PdfPCell(new Phrase(row.cpd_output_indicator_id + " " + row.cpd_output_indicator_question, tableDataFont));
                        myCell3.BorderColor = BaseColor.WHITE;
                        myCell3.BorderWidth = 1f;
                        myCell3.FixedHeight = 25f;
                        myCell3.VerticalAlignment = Element.ALIGN_MIDDLE;
                        t1.AddCell(myCell3);
                        PdfPCell myCell5 = new PdfPCell(new Phrase("Result", tableDataFont));
                        myCell5.BorderColor = BaseColor.WHITE;
                        myCell5.BorderWidth = 1f;
                        myCell5.FixedHeight = 35f;
                        myCell5.VerticalAlignment = Element.ALIGN_MIDDLE;
                        myCell5.HorizontalAlignment = Element.ALIGN_CENTER;
                        t1.AddCell(myCell5);
                        PdfPCell myCell6 = new PdfPCell(new Phrase(row.result, tableDataFont));
                        myCell6.BorderColor = BaseColor.WHITE;
                        myCell6.BorderWidth = 1f;
                        myCell6.FixedHeight = 35f;//This is the result height
                        myCell6.VerticalAlignment = Element.ALIGN_MIDDLE;
                        t1.AddCell(myCell6);
                        PdfPCell myCell7 = new PdfPCell(new Phrase("Comment", tableDataFont));
                        myCell7.BorderColor = BaseColor.WHITE;
                        myCell7.BorderWidth = 1f;
                        myCell7.FixedHeight = 25f;
                        myCell7.VerticalAlignment = Element.ALIGN_MIDDLE;
                        myCell7.HorizontalAlignment = Element.ALIGN_CENTER;
                        t1.AddCell(myCell7);
                        PdfPCell myCell8 = new PdfPCell(new Phrase(row.comment, tableDataFont));
                        myCell8.BorderColor = BaseColor.WHITE;
                        myCell8.BorderWidth = 1f;
                        myCell8.FixedHeight = 25f;
                        myCell8.VerticalAlignment = Element.ALIGN_MIDDLE;
                        t1.AddCell(myCell8);
                        PdfPCell t1Cell = new PdfPCell(t1);
                        //t1Cell.BackgroundColor = BaseColor.WHITE;
                        table.AddCell(t1Cell);

                        if (row.indicator_status_id == 1)
                        {
                            PdfPCell myCell4 = new PdfPCell(new Phrase("R", colorIndicatorDataFont));
                            //myCell4.Rowspan = 3;
                            myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                            myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                            myCell4.BackgroundColor = BaseColor.RED;
                            myCell4.BorderColor = BaseColor.WHITE;
                            myCell4.BorderWidth = 1f;
                            table.AddCell(myCell4);
                        }
                        else if (row.indicator_status_id == 2)
                        {
                            PdfPCell myCell4 = new PdfPCell(new Phrase("Y", colorIndicatorDataFont));
                            //myCell4.Rowspan = 3;
                            myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                            myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                            myCell4.BackgroundColor = BaseColor.YELLOW;
                            myCell4.BorderColor = BaseColor.WHITE;
                            myCell4.BorderWidth = 1f;
                            table.AddCell(myCell4);
                        }
                        else if (row.indicator_status_id == 3)
                        {
                            PdfPCell myCell4 = new PdfPCell(new Phrase("G", colorIndicatorDataFont));
                            //myCell4.Rowspan = 3;
                            myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                            myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                            myCell4.BackgroundColor = BaseColor.GREEN;
                            myCell4.BorderColor = BaseColor.WHITE;
                            myCell4.BorderWidth = 1f;
                            table.AddCell(myCell4);
                        }
                    }

                    foreach (var row in nonCpdIndicator)
                    {
                        PdfPCell myCell1 = new PdfPCell(new Phrase(row.region1, tableDataFont));
                        myCell1.BorderColor = BaseColor.WHITE;
                        myCell1.BorderWidth = 1f;
                        myCell1.VerticalAlignment = Element.ALIGN_MIDDLE;
                        myCell1.HorizontalAlignment = Element.ALIGN_CENTER;
                        table.AddCell(myCell1);

                        PdfPTable t1 = new PdfPTable(2);
                        float[] t1Widths = new float[] { 50f, 444f };
                        t1.SetTotalWidth(t1Widths);
                        PdfPCell myCell2 = new PdfPCell(new Phrase("Indicator", tableDataFont));
                        myCell2.BorderColor = BaseColor.WHITE;
                        myCell2.BorderWidth = 1f;
                        myCell2.FixedHeight = 25f;
                        myCell2.VerticalAlignment = Element.ALIGN_MIDDLE;
                        myCell2.HorizontalAlignment = Element.ALIGN_CENTER;
                        t1.AddCell(myCell2);
                        //PdfPCell myCell3 = new PdfPCell(new Phrase(row.cpd_output_id + " " + row.non_cpd_outp_indicator_question, tableDataFont));
                        PdfPCell myCell3 = new PdfPCell(new Phrase(row.non_cpd_outp_indicator_question, tableDataFont));
                        myCell3.BorderColor = BaseColor.WHITE;
                        myCell3.BorderWidth = 1f;
                        myCell3.FixedHeight = 25f;
                        myCell3.VerticalAlignment = Element.ALIGN_MIDDLE;
                        t1.AddCell(myCell3);
                        PdfPCell myCell5 = new PdfPCell(new Phrase("Result", tableDataFont));
                        myCell5.BorderColor = BaseColor.WHITE;
                        myCell5.BorderWidth = 1f;
                        myCell5.FixedHeight = 35f;
                        myCell5.VerticalAlignment = Element.ALIGN_MIDDLE;
                        myCell5.HorizontalAlignment = Element.ALIGN_CENTER;
                        t1.AddCell(myCell5);
                        PdfPCell myCell6 = new PdfPCell(new Phrase(row.result, tableDataFont));
                        myCell6.BorderColor = BaseColor.WHITE;
                        myCell6.BorderWidth = 1f;
                        myCell6.FixedHeight = 35f;
                        myCell6.VerticalAlignment = Element.ALIGN_MIDDLE;
                        t1.AddCell(myCell6);
                        PdfPCell myCell7 = new PdfPCell(new Phrase("Comment", tableDataFont));
                        myCell7.BorderColor = BaseColor.WHITE;
                        myCell7.BorderWidth = 1f;
                        myCell7.FixedHeight = 25f;
                        myCell7.VerticalAlignment = Element.ALIGN_MIDDLE;
                        myCell7.HorizontalAlignment = Element.ALIGN_CENTER;
                        t1.AddCell(myCell7);
                        PdfPCell myCell8 = new PdfPCell(new Phrase(row.comment, tableDataFont));
                        myCell8.BorderColor = BaseColor.WHITE;
                        myCell8.BorderWidth = 1f;
                        myCell8.FixedHeight = 25f;
                        myCell8.VerticalAlignment = Element.ALIGN_MIDDLE;
                        t1.AddCell(myCell8);
                        PdfPCell t1Cell = new PdfPCell(t1);
                        //t1Cell.BackgroundColor = BaseColor.WHITE;
                        table.AddCell(t1Cell);

                        if (row.indicator_status_id == 1)
                        {
                            PdfPCell myCell4 = new PdfPCell(new Phrase("R", colorIndicatorDataFont));
                            //myCell4.Rowspan = 3;
                            myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                            myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                            myCell4.BackgroundColor = BaseColor.RED;
                            myCell4.BorderColor = BaseColor.WHITE;
                            myCell4.BorderWidth = 1f;
                            table.AddCell(myCell4);
                        }
                        else if (row.indicator_status_id == 2)
                        {
                            PdfPCell myCell4 = new PdfPCell(new Phrase("Y", colorIndicatorDataFont));
                            //myCell4.Rowspan = 3;
                            myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                            myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                            myCell4.BackgroundColor = BaseColor.YELLOW;
                            myCell4.BorderColor = BaseColor.WHITE;
                            myCell4.BorderWidth = 1f;
                            table.AddCell(myCell4);
                        }
                        else if (row.indicator_status_id == 3)
                        {
                            PdfPCell myCell4 = new PdfPCell(new Phrase("G", colorIndicatorDataFont));
                            //myCell4.Rowspan = 3;
                            myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                            myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                            myCell4.BackgroundColor = BaseColor.GREEN;
                            myCell4.BorderColor = BaseColor.WHITE;
                            myCell4.BorderWidth = 1f;
                            table.AddCell(myCell4);
                        }
                    }
                }

                //===================CREATE NEW TABLE HERE====================================================
                PdfPTable nonSubOutcomeTable = new PdfPTable(3);

                float[] NonSubOutcomeWidths = new float[] { 30f, 494f, 14.5f };//Relative col widths in proportions
                nonSubOutcomeTable.SetTotalWidth(NonSubOutcomeWidths);

                nonSubOutcomeTable.SpacingBefore = 20f;
                nonSubOutcomeTable.SpacingAfter = 30f;
                //=========================END CREATE NEW TABLE HERE==========================================

                //int bit = 0;
                foreach (var row in NullNonCpdIndicator)
                {
                    /*if (bit == 0)
                    {
                        PdfPCell TitleCell = new PdfPCell(new Phrase("NON CPD OUTPUT INDICATORS "+counter/11, tableDataFont));
                        TitleCell.Colspan = 3;
                        //TitleCell.Phrase.Font.IsBold();
                        TitleCell.BackgroundColor = BaseColor.WHITE;
                        TitleCell.BorderColor = BaseColor.WHITE;
                        TitleCell.BorderWidth = 1f;
                        TitleCell.VerticalAlignment = Element.ALIGN_MIDDLE;
                        TitleCell.HorizontalAlignment = Element.ALIGN_CENTER;
                        nonSubOutcomeTable.AddCell(TitleCell);
                    }
                    bit = -1;*/
                    PdfPCell myCell1 = new PdfPCell(new Phrase(row.region1, tableDataFont));
                    myCell1.BorderColor = BaseColor.WHITE;
                    myCell1.BorderWidth = 1f;
                    myCell1.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell1.HorizontalAlignment = Element.ALIGN_CENTER;
                    nonSubOutcomeTable.AddCell(myCell1);

                    PdfPTable t1 = new PdfPTable(2);
                    float[] t1Widths = new float[] { 50f, 444f };
                    t1.SetTotalWidth(t1Widths);
                    PdfPCell myCell2 = new PdfPCell(new Phrase("Indicator", tableDataFont));
                    myCell2.BorderColor = BaseColor.WHITE;
                    myCell2.BorderWidth = 1f;
                    myCell2.FixedHeight = 25f;
                    myCell2.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell2.HorizontalAlignment = Element.ALIGN_CENTER;
                    t1.AddCell(myCell2);
                    //PdfPCell myCell3 = new PdfPCell(new Phrase(row.cpd_output_id + " " + row.non_cpd_outp_indicator_question, tableDataFont));
                    PdfPCell myCell3 = new PdfPCell(new Phrase(row.non_cpd_outp_indicator_question, tableDataFont));
                    myCell3.BorderColor = BaseColor.WHITE;
                    myCell3.BorderWidth = 1f;
                    myCell3.FixedHeight = 25f;
                    myCell3.VerticalAlignment = Element.ALIGN_MIDDLE;
                    t1.AddCell(myCell3);
                    PdfPCell myCell5 = new PdfPCell(new Phrase("Result", tableDataFont));
                    myCell5.BorderColor = BaseColor.WHITE;
                    myCell5.BorderWidth = 1f;
                    myCell5.FixedHeight = 35f;
                    myCell5.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell5.HorizontalAlignment = Element.ALIGN_CENTER;
                    t1.AddCell(myCell5);
                    PdfPCell myCell6 = new PdfPCell(new Phrase(row.result, tableDataFont));
                    myCell6.BorderColor = BaseColor.WHITE;
                    myCell6.BorderWidth = 1f;
                    myCell6.FixedHeight = 35f;
                    myCell6.VerticalAlignment = Element.ALIGN_MIDDLE;
                    t1.AddCell(myCell6);
                    PdfPCell myCell7 = new PdfPCell(new Phrase("Comment", tableDataFont));
                    myCell7.BorderColor = BaseColor.WHITE;
                    myCell7.BorderWidth = 1f;
                    myCell7.FixedHeight = 25f;
                    myCell7.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell7.HorizontalAlignment = Element.ALIGN_CENTER;
                    t1.AddCell(myCell7);
                    PdfPCell myCell8 = new PdfPCell(new Phrase(row.comment, tableDataFont));
                    myCell8.BorderColor = BaseColor.WHITE;
                    myCell8.BorderWidth = 1f;
                    myCell8.FixedHeight = 25f;
                    myCell8.VerticalAlignment = Element.ALIGN_MIDDLE;
                    t1.AddCell(myCell8);
                    PdfPCell t1Cell = new PdfPCell(t1);
                    //t1Cell.BackgroundColor = BaseColor.WHITE;
                    nonSubOutcomeTable.AddCell(t1Cell);

                    if (row.indicator_status_id == 1)
                    {
                        PdfPCell myCell4 = new PdfPCell(new Phrase("R", colorIndicatorDataFont));
                        //myCell4.Rowspan = 3;
                        myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                        myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                        myCell4.BackgroundColor = BaseColor.RED;
                        myCell4.BorderColor = BaseColor.WHITE;
                        myCell4.BorderWidth = 1f;
                        nonSubOutcomeTable.AddCell(myCell4);
                    }
                    else if (row.indicator_status_id == 2)
                    {
                        PdfPCell myCell4 = new PdfPCell(new Phrase("Y", colorIndicatorDataFont));
                        //myCell4.Rowspan = 3;
                        myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                        myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                        myCell4.BackgroundColor = BaseColor.YELLOW;
                        myCell4.BorderColor = BaseColor.WHITE;
                        myCell4.BorderWidth = 1f;
                        nonSubOutcomeTable.AddCell(myCell4);
                    }
                    else if (row.indicator_status_id == 3)
                    {
                        PdfPCell myCell4 = new PdfPCell(new Phrase("G", colorIndicatorDataFont));
                        //myCell4.Rowspan = 3;
                        myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                        myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                        myCell4.BackgroundColor = BaseColor.GREEN;
                        myCell4.BorderColor = BaseColor.WHITE;
                        myCell4.BorderWidth = 1f;
                        nonSubOutcomeTable.AddCell(myCell4);
                    }
                }

                foreach (var row in processIndicator)
                {
                    PdfPCell myCell1 = new PdfPCell(new Phrase(row.region1, tableDataFont));
                    myCell1.BorderColor = BaseColor.WHITE;
                    myCell1.BorderWidth = 1f;
                    myCell1.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell1.HorizontalAlignment = Element.ALIGN_CENTER;
                    nonSubOutcomeTable.AddCell(myCell1);

                    PdfPTable t1 = new PdfPTable(2);
                    float[] t1Widths = new float[] { 50f, 444f };
                    t1.SetTotalWidth(t1Widths);
                    PdfPCell myCell2 = new PdfPCell(new Phrase("Indicator", tableDataFont));
                    myCell2.BorderColor = BaseColor.WHITE;
                    myCell2.BorderWidth = 1f;
                    myCell2.FixedHeight = 25f;
                    myCell2.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell2.HorizontalAlignment = Element.ALIGN_CENTER;
                    t1.AddCell(myCell2);
                    PdfPCell myCell3 = new PdfPCell(new Phrase(row.internal_process_question, tableDataFont));
                    myCell3.BorderColor = BaseColor.WHITE;
                    myCell3.BorderWidth = 1f;
                    myCell3.FixedHeight = 25f;
                    myCell3.VerticalAlignment = Element.ALIGN_MIDDLE;
                    t1.AddCell(myCell3);
                    PdfPCell myCell5 = new PdfPCell(new Phrase("Result", tableDataFont));
                    myCell5.BorderColor = BaseColor.WHITE;
                    myCell5.BorderWidth = 1f;
                    myCell5.FixedHeight = 35f;
                    myCell5.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell5.HorizontalAlignment = Element.ALIGN_CENTER;
                    t1.AddCell(myCell5);
                    PdfPCell myCell6 = new PdfPCell(new Phrase(row.result, tableDataFont));
                    myCell6.BorderColor = BaseColor.WHITE;
                    myCell6.BorderWidth = 1f;
                    myCell6.FixedHeight = 35f;
                    myCell6.VerticalAlignment = Element.ALIGN_MIDDLE;
                    t1.AddCell(myCell6);
                    PdfPCell myCell7 = new PdfPCell(new Phrase("Comment", tableDataFont));
                    myCell7.BorderColor = BaseColor.WHITE;
                    myCell7.BorderWidth = 1f;
                    myCell7.FixedHeight = 25f;
                    myCell7.VerticalAlignment = Element.ALIGN_MIDDLE;
                    myCell7.HorizontalAlignment = Element.ALIGN_CENTER;
                    t1.AddCell(myCell7);
                    PdfPCell myCell8 = new PdfPCell(new Phrase(row.comment, tableDataFont));
                    myCell8.BorderColor = BaseColor.WHITE;
                    myCell8.BorderWidth = 1f;
                    myCell8.FixedHeight = 25f;
                    myCell8.VerticalAlignment = Element.ALIGN_MIDDLE;
                    t1.AddCell(myCell8);
                    PdfPCell t1Cell = new PdfPCell(t1);
                    //t1Cell.BackgroundColor = BaseColor.WHITE;
                    nonSubOutcomeTable.AddCell(t1Cell);

                    if (row.indicator_status_id == 1)
                    {
                        PdfPCell myCell4 = new PdfPCell(new Phrase("R", colorIndicatorDataFont));
                        //myCell4.Rowspan = 3;
                        myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                        myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                        myCell4.BackgroundColor = BaseColor.RED;
                        myCell4.BorderColor = BaseColor.WHITE;
                        myCell4.BorderWidth = 1f;
                        nonSubOutcomeTable.AddCell(myCell4);
                    }
                    else if (row.indicator_status_id == 2)
                    {
                        PdfPCell myCell4 = new PdfPCell(new Phrase("Y", colorIndicatorDataFont));
                        //myCell4.Rowspan = 3;
                        myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                        myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                        myCell4.BackgroundColor = BaseColor.YELLOW;
                        myCell4.BorderColor = BaseColor.WHITE;
                        myCell4.BorderWidth = 1f;
                        nonSubOutcomeTable.AddCell(myCell4);
                    }
                    else if (row.indicator_status_id == 3)
                    {
                        PdfPCell myCell4 = new PdfPCell(new Phrase("G", colorIndicatorDataFont));
                        //myCell4.Rowspan = 3;
                        myCell4.VerticalAlignment = Element.ALIGN_MIDDLE; //0=Left, 1=Centre, 2=Right
                        myCell4.HorizontalAlignment = Element.ALIGN_CENTER;
                        myCell4.BackgroundColor = BaseColor.GREEN;
                        myCell4.BorderColor = BaseColor.WHITE;
                        myCell4.BorderWidth = 1f;
                        nonSubOutcomeTable.AddCell(myCell4);
                    }
                }

                //IPdfPTableEvent tableEvent = new SubOutcomeAlternatingBackground();
                table.TableEvent = new SubOutcomeAlternatingBackground();
                nonSubOutcomeTable.TableEvent = new AlternatingBackground();

                //BEGIN PRINT THE TABLE
                int pageRows = 0;
                int currentPage;
                int newPage;
                if (reportPeriod == 4)
                {
                    currentPage = 3;
                    newPage = 4;
                }
                else
                {
                    currentPage = 2;
                    newPage = 3;
                }
                int pageRows2 = 0;
                while (pageRows <= table.Rows.Count)
                {
                    table.WriteSelectedRows(pageRows, pageRows + 11, 37, 735, pdfStamper.GetOverContent(currentPage));
                    pageRows += 11;

                    if (pageRows >= table.Rows.Count)
                    {
                        if (nonSubOutcomeTable.Rows.Count == 0)
                        {
                            break;
                        }
                    }

                    pdfStamper.InsertPage(newPage, pdfReader.GetPageSize(currentPage));
                    PdfContentByte content = pdfStamper.GetOverContent(newPage);
                    PdfImportedPage page;
                    if (reportPeriod == 4)
                    {
                        page = pdfStamper.GetImportedPage(pdfReader, 3);
                    }
                    else
                    {
                        page = pdfStamper.GetImportedPage(pdfReader, 2);
                    }
                    content.AddTemplate(page, 0, 0);
                    newPage++;
                    currentPage++;
                }
                
                while (pageRows2 <= nonSubOutcomeTable.Rows.Count)
                {
                    nonSubOutcomeTable.WriteSelectedRows(pageRows2, pageRows2 + 8, 37, 735, pdfStamper.GetOverContent(currentPage));
                    pageRows2 += 8;
                    if (pageRows2 >= nonSubOutcomeTable.Rows.Count)
                    {
                        break;
                    }
                    pdfStamper.InsertPage(newPage, pdfReader.GetPageSize(2));
                    PdfContentByte content2 = pdfStamper.GetOverContent(newPage);
                    PdfImportedPage page2;
                    if (reportPeriod == 4)
                    {
                        page2 = pdfStamper.GetImportedPage(pdfReader, 3);
                    }
                    else
                    {
                        page2 = pdfStamper.GetImportedPage(pdfReader, 2);
                    }
                    content2.AddTemplate(page2, 0, 0);
                    newPage++;
                    currentPage++;
                }
                //END PRINT THE TABLE


                /*//PRINT OUT THE TABLE
                //CREATE A NEW PAGE IF IT DOES NOT FIT IN THE ORIGINAL PAGE
                if (table.Rows.Count <= 8)
                {
                    table.WriteSelectedRows(0, -1, 37, 735, pdfStamper.GetOverContent(2));
                }
                else
                {
                    int newPage = 3;
                    int currentPage = 2;
                    int lastLinePrinted = 0;
                    int maxLine = 8;
                    bool lastPage = false;
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        // must decrement the table.Rows.Count here, 
                        // otherwise it'll never enter the code below, we'll
                        // increment it back when we reach the last page
                        if ((i > 0 && i % 8 == 0) || i == table.Rows.Count - 1)
                        {
                            PdfContentByte cb = pdfStamper.GetOverContent(currentPage);
                            table.WriteSelectedRows(lastLinePrinted, maxLine, 37, 735, cb);

                            if (!lastPage)
                            {
                                pdfStamper.InsertPage(newPage, pdfReader.GetPageSize(2));
                                //Duplicate the old page templete to the new page
                                PdfContentByte content = pdfStamper.GetOverContent(newPage);
                                PdfImportedPage page = pdfStamper.GetImportedPage(pdfReader, 2);
                                content.AddTemplate(page, 0, 0);

                                //PdfContentByte cb2 = pdfStamper.GetOverContent(currentPage);
                                table.WriteSelectedRows(lastLinePrinted, maxLine, 37, 735, cb);

                                newPage++;
                                currentPage++;
                                lastLinePrinted = maxLine;
                                maxLine += 8;
                            }
                            if (maxLine > table.Rows.Count)
                            {
                                maxLine = table.Rows.Count + 1;
                                lastPage = true;
                            }
                        }
                        // This prints out the last row incase a single
                        // row remains and flows out to the last page
                        PdfContentByte cb2 = pdfStamper.GetOverContent(currentPage);
                        table.WriteSelectedRows(lastLinePrinted, maxLine, 37, 735, cb2);
                    }
                }*/
                //=========================================================+
                //          <END> GENERATE THE INDICATOR TABLE             |
                //=========================================================+
            }
            catch (Exception ex)
            {
                throw new Exception("PDF ERROR " + ex.Message + " " + ex.StackTrace);
            }
        }

        public PdfReader CreateReaderFromTemplate(long project, long reportPeriod, long year)
        {
            //=======================================+
            //          NEW PDF TEMPLATE             |
            //=======================================+

            string pdfTemplate;
            try
            {
                var draft = (from p in db.project_reports
                                where
                                    p.project_id == project &&
                                    p.year_id == year &&
                                    p.report_period_id == reportPeriod
                                select p.project_status_id).Single();

                if (draft == 1)
                {
                    if ((reportPeriod <= 2 && year <= 2) || year == 1)
                    {
                        pdfTemplate = System.Web.HttpContext.Current.Server.MapPath("~/doc/M&E Quarterly Report Design Template.DRAFT STATUS.V4.pdf");
                    }
                    else
                    {
                        if (reportPeriod == 4)
                        {
                            pdfTemplate = System.Web.HttpContext.Current.Server.MapPath("~/doc/_M&E Quarterly Report Design Template.DRAFT.V8.1.pdf");
                        }
                        else
                        {
                            pdfTemplate = System.Web.HttpContext.Current.Server.MapPath("~/doc/M&E Quarterly Report Design Template.DRAFT.V8.pdf");
                        }
                    }
                }
                else
                {
                    if ((year <= 2 && reportPeriod <= 2) || year == 1)
                    {
                        pdfTemplate = System.Web.HttpContext.Current.Server.MapPath("~/doc/M&E Quarterly Report Design Template.V4.pdf");
                    }
                    else
                    {
                        if (reportPeriod == 4)
                        {
                            pdfTemplate = System.Web.HttpContext.Current.Server.MapPath("~/doc/_M&E Quarterly Report Design Template.FINAL.V8.1.pdf");
                        }
                        else
                        {
                            pdfTemplate = System.Web.HttpContext.Current.Server.MapPath("~/doc/M&E Quarterly Report Design Template.FINAL.V8.pdf");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("DATABASE ERROR " + ex.Message + "<br />" + ex.StackTrace);
            }
            return new PdfReader(pdfTemplate);
        }

        public void GeneratePDF(long project, long reportPeriod, long year)
        {
            PdfReader pdfReader = CreateReaderFromTemplate(project, reportPeriod, year);

            MemoryStream pdfOutputFile = new MemoryStream();
            pdfOutputFile.Seek(0, SeekOrigin.Begin);

            //Create the form filler
            PdfStamper pdfStamper = new PdfStamper(pdfReader, pdfOutputFile);

            //======================================================+
            //        <BEGIN>  Insert data into the Template        |
            //======================================================+
            PopulateAcroFields(project, reportPeriod, year, pdfStamper);

            /*if ((year <= 2 && reportPeriod <= 3) || year == 1)
            {*/
                GenerateIndicatorsTable(project, reportPeriod, year, pdfReader, pdfStamper);
            /*}
            else
            {
                _GenerateIndicatorsTable(project, reportPeriod, year, pdfReader, pdfStamper);
            }*/
            //======================================================+
            //       </BEGIN>   Insert data into the Template       |
            //======================================================+

            pdfStamper.FormFlattening = true;
            pdfStamper.Close();
            pdfOutputFile.Close();
            pdfReader.Close();

            Response.Buffer = false;
            Response.Clear();
            Response.ClearContent();
            Response.ClearHeaders();

            // get the generated PDF as raw data
            byte[] templateData = ModifyPDF.AddPageNumbers(pdfOutputFile.ToArray());

            // write the document data to response stream and set appropriate headers:
            Response.AppendHeader("Content-Length", templateData.Length.ToString());

            string downloadTitle;

            try
            {
                var title = (from t in db.project_reports
                             where
                                 t.project_id == project &&
                                 t.year_id == year &&
                                 t.report_period_id == reportPeriod
                             select new
                             {
                                 t.project.project1,
                                 t.year.year1,
                                 t.report_period.report_period1
                             });
                foreach (var myTitle in title)
                {
                    downloadTitle = myTitle.project1.Trim() + " " + myTitle.report_period1.Trim() + " " + myTitle.year1.Trim();
                    Response.AppendHeader("Content-Disposition", "attachment; filename=" + downloadTitle + ".pdf");
                }
            }
            catch (Exception ex)
            {
                throw new Exception("PDF ERROR " + ex.Message + " " + ex.StackTrace);
            }
            Response.ContentType = "application/pdf";
            Response.OutputStream.Write(templateData, 0, templateData.Length);
            Response.OutputStream.Flush();
            //call the HttpContext.Current.ApplicationInstance.CompleteRequest method 
            //instead of Response.End to bypass the code execution to the Application_EndRequest event.
            //Response.End();
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }

        public void CreatePDF(long project, long reportPeriod, long year)
        {
            GeneratePDF(project, reportPeriod, year);
        }
//&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
        public class ModifyPDF
        {
            public static byte[] AddPageNumbers(byte[] pdf)
            {
                MemoryStream ms = new MemoryStream();
                try
                {
                    // add page numbers
                    Document copyDoc = new Document();

                    //This part can be replaced
                    PdfCopy copyPdf = new PdfCopy(copyDoc, ms);
                    //TEST
                    //PdfCopy copyPdf = new PdfCopy(copyDoc, new FileStream("D:/pdf/Test_Created.pdf", FileMode.Create));
                    copyPdf.SetPageSize(PageSize.A4.Rotate());
                    copyDoc.Open();

                    // read the initial pdf document
                    PdfReader reader = new PdfReader(pdf);
                    int totalPages = reader.NumberOfPages;

                    PdfImportedPage copiedPage = null;
                    iTextSharp.text.pdf.PdfCopy.PageStamp stamper = null;

                    for (int i = 0; i < totalPages; i++)
                    {
                        // get the page and create a stamper for that page
                        copiedPage = copyPdf.GetImportedPage(reader, (i + 1));
                        stamper = copyPdf.CreatePageStamp(copiedPage);

                        // add a page number to the page
                        ColumnText.ShowTextAligned(stamper.GetUnderContent(),
                            Element.ALIGN_CENTER, new Phrase((i + 1) + "/" + totalPages,
                                FontFactory.GetFont("Helvetica", 10, Font.NORMAL, BaseColor.BLACK)),
                                550,
                                24,
                                0);
                        stamper.AlterContents();

                        // add the altered page to the new document
                        copyPdf.AddPage(copiedPage);
                    }
                    copyDoc.Close();
                    reader.Close();
                }
                catch (Exception ex)
                {
                    throw new Exception("An error ocurred, the PDF-document could not be created." + ex.Message);
                }
                return ms.ToArray();
            }
//==============================THIS WORKS ALSO (BUT WITH ERRORS) UNUSED ======(BEGIN)=====================================
            public static byte[] AddPageNumbers_Test(byte[] pdf)
            {
                MemoryStream ms = new MemoryStream();
                ms.Seek(0, SeekOrigin.Begin);
                ms.Write(pdf, 0, pdf.Length);

                //We create a reader for a certain document
                PdfReader reader = new PdfReader(pdf);

                //We retrieve the total number of pages
                int n = reader.NumberOfPages;

                //We retieve the size of the first page
                Rectangle pSize = reader.GetPageSize(1);

                //Step 1: Creation of a document-object
                Document document = new Document(pSize, 50, 50, 50, 50);
                //Step 2: We create a writer that listens to the document
                PdfWriter writer = PdfWriter.GetInstance(document, ms);
                //Step 3: We open the document
                document.Open();
                //Step 4: We add Content
                PdfContentByte cb = writer.DirectContent;

                int p = 0;
                for (int page = 1; page <= reader.NumberOfPages; page++)
                {
                    document.NewPage();
                    p++;

                    PdfImportedPage importedPage = writer.GetImportedPage(reader, page);
                    cb.AddTemplate(importedPage, 0, 0);

                    string fontPath = System.Web.HttpContext.Current.Server.MapPath("~/Styles/");
                    BaseFont MyriadPro = BaseFont.CreateFont(fontPath + "MyriadPro-Regular.otf", BaseFont.CP1252, BaseFont.NOT_EMBEDDED);
                    cb.BeginText();
                    cb.SetFontAndSize(MyriadPro, 10);
                    cb.ShowTextAligned(PdfContentByte.ALIGN_LEFT, p + "/" + n, 550, 24, 0);
                    cb.EndText();
                }
                //Step 5: We Close the document
                document.Close();
                writer.Close();
                reader.Close();
                return ms.ToArray();
            }
//==============================THIS WORKS ALSO (BUT WITH ERRORS) UNUSED =======(END)=====================================
        }
//&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
        public class SubOutcomeAlternatingBackground : IPdfPTableEvent
        {
            public void TableLayout(PdfPTable table, float[][] widths,
                float[] heights, int headerRows, int rowStart, PdfContentByte[] canvases)
            {
                try
                {
                    int columns;
                    Rectangle rect;
                    int footer = widths.Length - table.FooterRows;
                    int header = table.HeaderRows - table.FooterRows + 1;
                    for (int row = 0; row < footer; row += 2)
                    {
                        columns = widths[row].Length - 1;
                        rect = new Rectangle(widths[row][0], heights[row],
                                    widths[row][columns], heights[row + 1]);
                        rect.BackgroundColor = new BaseColor(System.Drawing.ColorTranslator.FromHtml("#f2f2f2"));
                        rect.Border = Rectangle.NO_BORDER;
                        canvases[PdfPTable.BASECANVAS].Rectangle(rect);
                    }
                    for (int row = 1; row < footer; row += 2)
                    {
                        columns = widths[row].Length - 1;
                        rect = new Rectangle(widths[row][0], heights[row],
                                    widths[row][columns], heights[row + 1]);
                        //rect.BackgroundColor = BaseColor.LIGHT_GRAY;
                        rect.BackgroundColor = new BaseColor(System.Drawing.ColorTranslator.FromHtml("#f2f2f2"));
                        rect.Border = Rectangle.NO_BORDER;
                        canvases[PdfPTable.BASECANVAS].Rectangle(rect);
                    }
                }
                catch (Exception ex)
                {
                    throw new NotImplementedException(ex.Message + " " + ex.StackTrace);
                }
            }
        }
//&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
        public class AlternatingBackground : IPdfPTableEvent
        {
            public void TableLayout(PdfPTable table, float[][] widths,
                float[] heights, int headerRows, int rowStart, PdfContentByte[] canvases)
            {
                try
                {
                    int columns;
                    Rectangle rect;
                    int footer = widths.Length - table.FooterRows;
                    int header = table.HeaderRows - table.FooterRows + 1;
                    for (int row = 0; row < footer; row += 2)
                    {
                        columns = widths[row].Length - 1;
                        rect = new Rectangle(widths[row][0], heights[row],
                                    widths[row][columns], heights[row + 1]);
                        rect.BackgroundColor = new BaseColor(System.Drawing.ColorTranslator.FromHtml("#f2f2f2"));
                        rect.Border = Rectangle.NO_BORDER;
                        canvases[PdfPTable.BASECANVAS].Rectangle(rect);
                    }
                    for (int row = 1; row < footer; row += 2)
                    {
                        columns = widths[row].Length - 1;
                        rect = new Rectangle(widths[row][0], heights[row],
                                    widths[row][columns], heights[row + 1]);
                        rect.BackgroundColor = BaseColor.LIGHT_GRAY;
                        //rect.BackgroundColor = new BaseColor(System.Drawing.ColorTranslator.FromHtml("#f2f2f2"));
                        rect.Border = Rectangle.NO_BORDER;
                        canvases[PdfPTable.BASECANVAS].Rectangle(rect);
                    }
                }
                catch (Exception ex)
                {
                    throw new NotImplementedException(ex.Message + " " + ex.StackTrace);
                }
            }
        }
//&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
    }
}